﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using WhereIsMyCash.IDAL;
using WhereIsMyCash.Entity;
using WhereIsMyCash.Enums;
using WhereIsMyCash.Data;

namespace WhereIsMyCash.SQLServerDAL
{
    public class Contact : IContact
    {
        #region 私有成员

        /// <summary>
        /// 绑定实体数据
        /// </summary>
        /// <param name="contact">实体</param>
        /// <param name="reader">IDataReader</param>
        void BindEntity(ContactInfo contact, IDataReader reader)
        {
            object objData;

            objData = reader["ContactID"];
            if (objData != DBNull.Value)
            {
                contact.ID = (int)objData;
            }

            contact.Name = reader["ContactName"].ToString();

            contact.Phone = reader["Phone"].ToString();

            contact.Email = reader["Email"].ToString();

            contact.Address = reader["Address"].ToString();

            objData = reader["GroupID"];
            if (objData != DBNull.Value)
            {
                contact.GroupID = (int)objData;
            }

            contact.Remark = reader["Remark"].ToString();

            objData = reader["IsDel"];
            if (objData != DBNull.Value)
            {
                contact.IsDel = Convert.ToBoolean(reader["IsDel"]);
            }
        }

        /// <summary>
        /// 根据sql语句返回联系人实体
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        ContactInfo GetEntityBySQL(string sql)
        {
            ContactInfos contacts = GetEntitisBySQL(sql);

            if (contacts.Count > 0)
            {
                return contacts[0];
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据sql语句返回实体列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        ContactInfos GetEntitisBySQL(string sql)
        {
            ContactInfos contacts = new ContactInfos();

            using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql))
            {
                try
                {
                    while (reader.Read())
                    {
                        ContactInfo contact = new ContactInfo();

                        BindEntity(contact, reader);
                        contacts.Add(contact);
                    }
                }
                catch
                {
                    //记录错误
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }

            return contacts;
        }

        #endregion

        #region ICRUD<ContactInfo> 成员

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="entity">联系人实体</param>
        /// <param name="checkType">检测类型</param>
        /// <returns></returns>

        bool ICRUD<ContactInfo>.IsExist(ContactInfo entity, WhereIsMyCash.Enums.EnumExsistingCheckType checkType)
        {
            bool isExist = false;
            string sql = string.Empty;
            object objData = null;
            SqlParameter[] parms = { SqlHelper.MakeInParam("@ContactID", SqlDbType.Int, 4, entity.ID), SqlHelper.MakeInParam("@ContactName", SqlDbType.NVarChar, 50, entity.Name) };

            switch (checkType)
            {
                case EnumExsistingCheckType.一般性检测:
                    sql = "SELECT 1 FROM [Contact] WHERE [ContactID] = @ContactID";
                    break;
                case EnumExsistingCheckType.添加时检测:
                    sql = "SELECT 1 FROM [Contact] WHERE [ContactName] = @ContactName";
                    break;
                case EnumExsistingCheckType.更新时检测:
                    sql = "SELECT 1 FROM [Contact] WHERE [ContactName] = @ContactName AND [ContactID] <> @ContactID";
                    break;
                default:
                    break;
            }

            objData = SqlHelper.ExecuteScalar(CommandType.Text, sql, parms);

            if (null != objData)
            {
                isExist = true;
            }

            return isExist;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="entity">联系人实体</param>
        /// <returns>添加成功后的ID</returns>
        int ICRUD<ContactInfo>.Add(ContactInfo entity)
        {
            int id = 0;
            object objData = null;
            StringBuilder sql = new StringBuilder();
            SqlParameter[] parms = 
            { 
                SqlHelper.MakeInParam("@ContactName", SqlDbType.NVarChar, 100, entity.Name),
                SqlHelper.MakeInParam("@Phone", SqlDbType.VarChar, 20, entity.Phone),
                SqlHelper.MakeInParam("@Email", SqlDbType.VarChar, 20, entity.Email),
                SqlHelper.MakeInParam("@Address", SqlDbType.NVarChar, 150, entity.Address),
                SqlHelper.MakeInParam("@GroupID", SqlDbType.Int, 4, entity.GroupID),
                SqlHelper.MakeInParam("@Remark", SqlDbType.NText, 0, entity.Remark)
            };

            sql.Append("INSERT INTO [Contact] ")
                .Append("([ContactName],[Phone],[Email],[Address],[GroupID][Remark]) ")
                .Append("VALUES (@ContactName,@Phone,@Email,@Address,@GroupID,@Remark) ")
                .Append("SELECT @@IDENTITY");

            objData = SqlHelper.ExecuteScalar(CommandType.Text, sql.ToString(), parms);

            if (null != objData)
            {
                id = (int)objData;
            }

            return id;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity">联系人实体</param>
        void ICRUD<ContactInfo>.Update(ContactInfo entity)
        {
            StringBuilder sql = new StringBuilder();
            SqlParameter[] parms = 
            { 
                SqlHelper.MakeInParam("@ContactID", SqlDbType.Int, 4, entity.ID),
                SqlHelper.MakeInParam("@ContactName", SqlDbType.NVarChar, 100, entity.Name),
                SqlHelper.MakeInParam("@Phone", SqlDbType.VarChar, 20, entity.Phone),
                SqlHelper.MakeInParam("@Email", SqlDbType.VarChar, 20, entity.Email),
                SqlHelper.MakeInParam("@Address", SqlDbType.NVarChar, 150, entity.Address),
                SqlHelper.MakeInParam("@GroupID", SqlDbType.Int, 4, entity.GroupID),
                SqlHelper.MakeInParam("@Remark", SqlDbType.NText, 0, entity.Remark)
            };

            sql.Append("UPDATE [Contact] ")
                .Append("SET ")
                .Append("[ContactName] = @ContactName, ")
                .Append("[Phone] = @Phone, ")
                .Append("[Email] = @Email, ")
                .Append("[Address] = @Address, ")
                .Append("[GroupID] = @GroupID, ")
                .Append("[Remark] = @Remark")
                .Append("WHERE [ContactID] = @ContactID");

            SqlHelper.ExecuteNonQuery(CommandType.Text, sql.ToString(), parms);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="Id"></param>
        void ICRUD<ContactInfo>.Delete(int Id)
        {
            string sql = "UPDATE [Contact] SET [IsDel] = 1 WHERE [ContactID] = @ContactID";
            SqlParameter[] parms = { SqlHelper.MakeInParam("@ContactID", SqlDbType.Int, 4, Id) };

            SqlHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        /// <summary>
        /// 返回联系人实体
        /// </summary>
        /// <param name="Id">联系人ID</param>
        /// <returns></returns>
        ContactInfo ICRUD<ContactInfo>.GetInfo(int Id)
        {
            ContactInfo Contact = null;
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT [ContactID],[ContactName],[Phone],[Email],[Address],[GroupID],[Remark],[IsDel] ")
                .Append("FROM [Contact] ")
                .AppendFormat("WHERE [ContactID] = {0}", Id);

            Contact = GetEntityBySQL(sql.ToString());

            return Contact;
        }

        /// <summary>
        /// 返回联系人列表
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        List<ContactInfo> ICRUD<ContactInfo>.GetList(string condition)
        {
            ContactInfos Contacts = new ContactInfos();
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT [ContactID],[ContactName],[Phone],[Email],[Address],[GroupID],[Remark],[IsDel] ")
                .Append("FROM [Contact] ")
                .AppendFormat("WHERE {0}", condition);

            Contacts = GetEntitisBySQL(sql.ToString());

            return Contacts;
        }

        /// <summary>
        /// 返回联系人列表
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="curPage">当前页码</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        List<ContactInfo> ICRUD<ContactInfo>.GetList(int pageSize, int curPage, string condition)
        {
            ContactInfos Contacts = new ContactInfos();
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("SELECT TOP {0} [ContactID],[ContactName],[Phone],[Email],[Address],[GroupID],[Remark],[IsDel] ", pageSize)
                .Append("FROM [Contact] ")
                .Append("WHERE ")
                .Append("[ContactID] NOT IN( ")
                .AppendFormat("SELECT TOP {0} [ContactID] ", pageSize * (curPage - 1))
                .Append("FROM [Contact] ")
                .AppendFormat("WHERE {0} )", condition)
                .AppendFormat("AND {0} )", condition);

            Contacts = GetEntitisBySQL(sql.ToString());

            return Contacts;
        }

        /// <summary>
        /// 返回记录条数
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        int ICRUD<ContactInfo>.GetCount(string condition)
        {
            object objDate = null;
            string sql = string.Format("SELECT COUNT(*) FROM [Contact] WHERE {0}", condition);

            objDate = SqlHelper.ExecuteScalar(CommandType.Text, sql);

            return (int)(objDate ?? 0);
        }

        #endregion
    }
}
